﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
using Utility;
namespace Service
{
    /// <summary>
    /// 功能：HW_Manger_Admin_User类数据访问
    /// 创建人：Wilson
    /// 创建时间：2013-1-30   
    /// </summary>
    public class HW_Manger_Admin_UserService : IDisposable
    {
        #region 公共
        private System.ComponentModel.Component component = new System.ComponentModel.Component();

        private bool disposed = false;

        ~HW_Manger_Admin_UserService()
        {
            Dispose(false);
        }

        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (!this.disposed)
            {

                if (disposing)
                {

                    component.Dispose();
                }

                disposed = true;

            }
        }
        #endregion
		#region##MapperHW_Manger_Admin_User
        public IList<HW_Manger_Admin_User> MapperList(SqlDataReader reader)
         {
            IList<HW_Manger_Admin_User> list = new List<HW_Manger_Admin_User>();
            HW_Manger_Admin_User obj=null;
            HW_Admin_Detail detail = null;
            HW_District distict = null;
            while (reader.Read())
            {
                obj = new HW_Manger_Admin_User();
                obj.XX_UserName = (string)reader["XX_UserName"];
                obj.DistrictCode = (string)reader["DistrictCode"];
                obj.RoleId = reader["RoleId"] as int?;
                obj.XX_Pass = reader["XX_Pass"] as string;
                obj.CNName = reader["CNName"] as string;
                obj.ErrorTimes = reader["ErrorTimes"] as int?;
                obj.LastLoginTime = reader["LastLoginTime"] as DateTime?;
                obj.Version = reader["Version"] as int?;
                obj.CityId = (string)reader["CityId"];

                detail = new HW_Admin_Detail();
                detail.XX_UserName = (string)reader["XX_UserName"];
                detail.WorkUnit = reader["WorkUnit"] as string;
                detail.Address = reader["Address"] as string;
                detail.Email = reader["Email"] as string;
                detail.MobilePhone = reader["MobilePhone"] as string;
                detail.OfficePhone = reader["OfficePhone"] as string;
                detail.ZipCode = reader["ZipCode"] as string;
                detail.Version = (int)reader["Version"];
                detail.Fax = reader["Fax"] as string;

                distict = new HW_District();
                distict.DistrictCode = (string)reader["DistrictCode"];
                distict.Name = (string)reader["Name"];
                distict.Sort = reader["Sort"] as int?;
                distict.ParentDistrictCode = reader["ParentDistrictCode"] as string;
                distict.Version = (int)reader["Version"];
                distict.Level = (int)reader["Level"];
                distict.SchoolNumber = reader["SchoolNumber"] as int?;

                obj.hW_Admin_Detail = detail;
                obj.hW_District = distict;
                list.Add(obj);
            }
            return list;
         }
        public HW_Manger_Admin_User Mapper(SqlDataReader reader)
         {
             HW_Manger_Admin_User obj = null;
             HW_Admin_Detail detail = null;
             HW_District distict = null;
            if (reader.Read())
            {
                obj = new HW_Manger_Admin_User();
                obj.XX_UserName = (string)reader["XX_UserName"];
                obj.DistrictCode = (string)reader["DistrictCode"];
                obj.RoleId = reader["RoleId"] as int?;
                //obj.XX_Pass = reader["XX_Pass"] as string;
                obj.CNName = reader["CNName"] as string;
                obj.ErrorTimes = reader["ErrorTimes"] as int?;
                obj.LastLoginTime = reader["LastLoginTime"] as DateTime?;
                obj.Version = reader["Version"] as int?;
                obj.CityId = (string)reader["CityId"];

                detail = new HW_Admin_Detail();
                detail.XX_UserName = (string)reader["XX_UserName"];
                detail.WorkUnit = reader["WorkUnit"] as string;
                detail.Address = reader["Address"] as string;
                detail.Email = reader["Email"] as string;
                detail.MobilePhone = reader["MobilePhone"] as string;
                detail.OfficePhone = reader["OfficePhone"] as string;
                detail.ZipCode = reader["ZipCode"] as string;
                detail.Version = (int)reader["Version"];
                detail.Fax = reader["Fax"] as string;

                distict = new HW_District();
                distict.DistrictCode = (string)reader["DistrictCode"];
                distict.Name = (string)reader["Name"];
                distict.Sort = reader["Sort"] as int?;
                distict.ParentDistrictCode = reader["ParentDistrictCode"] as string;
                distict.Version = (int)reader["Version"];
                distict.Level = (int)reader["Level"];
                distict.SchoolNumber = reader["SchoolNumber"] as int?;

                obj.hW_Admin_Detail = detail;
                obj.hW_District = distict;
            }
            return obj;
         }
        #endregion
        #region##通过主键ID得到HW_Manger_Admin_User
        /// <summary>
        /// 功能：通过ID得到HW_Manger_Admin_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
        public HW_Manger_Admin_User GetById(string xx_username)
        {
            string sql = "select a.*,WorkUnit,Address,Email,MobilePhone,OfficePhone,ZipCode,Fax,c.* from HW_Manger_Admin_User a left join HW_Admin_Detail b on a.XX_UserName=b.XX_UserName inner join HW_District c on a.DistrictCode=c.DistrictCode where a.XX_UserName = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",xx_username)
            };
            
            SqlDataReader reader=SqlHelper.ExecuteReader(WebConn.connString,CommandType.Text,sql,sp);
            return Mapper(reader);
        }
        public int  ResetPassword(string xx_username,string pass)
        {
            string sql = "update HW_Manger_Admin_User set XX_Pass=@pass";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pass",pass)
            };
            int result = SqlHelper.ExecuteNonQuery(WebConn.connString, CommandType.Text, sql, sp);
            return result;
        }
        public int ResetPassword(SqlTransaction trans,string xx_username, string pass)
        {
            string sql = "update HW_Manger_Admin_User set XX_Pass=@pass where XX_UserName=@xx_username";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pass",pass),
                new SqlParameter("@xx_username",xx_username),
            };
            int result = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sql, sp);
            return result;
        }
        #endregion
		
		#region##通过主键ID删除HW_Manger_Admin_User
        /// <summary>
        /// 功能：通过主键ID删除HW_Manger_Admin_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
		public int DeleteById(string xx_username)
		{
			int i = 0;
			string sql = "delete from HW_Manger_Admin_User where XX_UserName = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",50)
            };
            i= SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
			return i;
		}
        /// <summary>
        /// 功能：通过主键ID删除HW_Manger_Admin_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
		public int DeleteById(SqlTransaction transaction,string xx_username)
		{
			int i = 0;
			string sql = "delete from HW_Manger_Admin_User where XX_UserName = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",50)
            };
            i= SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
			return i;
		}
		#endregion			
		
        #region##添加HW_Manger_Admin_User
        /// <summary>
        /// 功能：添加HW_Manger_Admin_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Add(HW_Manger_Admin_User hw_manger_admin_user)
        {
            int result = 0;
            string sql = "insert into HW_Manger_Admin_User(XX_UserName,DistrictCode,RoleId,XX_Pass,CNName,ErrorTimes,LastLoginTime,Version,CityId) values(@XX_UserName,@DistrictCode,@RoleId,@XX_Pass,@CNName,@ErrorTimes,@LastLoginTime,@Version,@CityId)";			
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@XX_UserName",hw_manger_admin_user.XX_UserName),
				new SqlParameter("@DistrictCode",hw_manger_admin_user.DistrictCode),
				new SqlParameter("@RoleId",hw_manger_admin_user.RoleId),
				new SqlParameter("@XX_Pass",hw_manger_admin_user.XX_Pass),
				new SqlParameter("@CNName",hw_manger_admin_user.CNName),
				new SqlParameter("@ErrorTimes",hw_manger_admin_user.ErrorTimes),
				new SqlParameter("@LastLoginTime",hw_manger_admin_user.LastLoginTime),
				new SqlParameter("@Version",hw_manger_admin_user.Version),
				new SqlParameter("@CityId",hw_manger_admin_user.CityId)
            };
            result = SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
            return result;
            
        }
         /// <summary>
        /// 功能：添加HW_Manger_Admin_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Add(SqlTransaction transaction,HW_Manger_Admin_User hw_manger_admin_user)
        {
            int result = 0;
            string sql = "insert into HW_Manger_Admin_User(XX_UserName,DistrictCode,RoleId,XX_Pass,CNName,ErrorTimes,LastLoginTime,Version,CityId) values(@XX_UserName,@DistrictCode,@RoleId,@XX_Pass,@CNName,@ErrorTimes,@LastLoginTime,@Version,@CityId)";			
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@XX_UserName",hw_manger_admin_user.XX_UserName),
				new SqlParameter("@DistrictCode",hw_manger_admin_user.DistrictCode),
				new SqlParameter("@RoleId",hw_manger_admin_user.RoleId),
				new SqlParameter("@XX_Pass",hw_manger_admin_user.XX_Pass),
				new SqlParameter("@CNName",hw_manger_admin_user.CNName),
				new SqlParameter("@ErrorTimes",hw_manger_admin_user.ErrorTimes),
				new SqlParameter("@LastLoginTime",hw_manger_admin_user.LastLoginTime),
				new SqlParameter("@Version",hw_manger_admin_user.Version),
				new SqlParameter("@CityId",hw_manger_admin_user.CityId)
            };
            result = SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
            return result;
        }
        #endregion
		
		#region##得到HW_Manger_Admin_User集合
		/// <summary>
        /// 功能：得到HW_Manger_Admin_User集合
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>       
        /// <returns></returns>
		public IList<HW_Manger_Admin_User> GetList()
		{
			string sql = "select * from HW_Manger_Admin_User";			
			SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString,CommandType.Text,sql);
            return MapperList(reader);
		}
		#endregion
		
		#region##修改HW_Manger_Admin_User
        /// <summary>
        /// 功能：修改HW_Manger_Admin_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int ModHW_Manger_Admin_User(HW_Manger_Admin_User hw_manger_admin_user)
        {
            int result = 0;
            string sql = "update HW_Manger_Admin_User set XX_UserName = @XX_UserName,DistrictCode = @DistrictCode,RoleId = @RoleId,XX_Pass = @XX_Pass,CNName = @CNName,ErrorTimes = @ErrorTimes,LastLoginTime = @LastLoginTime,Version = @Version,CityId = @CityId where XX_UserName = @XX_UserName";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@XX_UserName",hw_manger_admin_user.XX_UserName),
				new SqlParameter("@DistrictCode",hw_manger_admin_user.DistrictCode),
				new SqlParameter("@RoleId",hw_manger_admin_user.RoleId),
				new SqlParameter("@XX_Pass",hw_manger_admin_user.XX_Pass),
				new SqlParameter("@CNName",hw_manger_admin_user.CNName),
				new SqlParameter("@ErrorTimes",hw_manger_admin_user.ErrorTimes),
				new SqlParameter("@LastLoginTime",hw_manger_admin_user.LastLoginTime),
				new SqlParameter("@Version",hw_manger_admin_user.Version),
				new SqlParameter("@CityId",hw_manger_admin_user.CityId)
            };
            result = SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
            return result;
        }
         /// <summary>
        /// 功能：修改HW_Manger_Admin_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int ModHW_Manger_Admin_User(SqlTransaction transaction,HW_Manger_Admin_User hw_manger_admin_user)
        {
            int result = 0;
            string sql = "update HW_Manger_Admin_User set XX_UserName = @XX_UserName,DistrictCode = @DistrictCode,RoleId = @RoleId,XX_Pass = @XX_Pass,CNName = @CNName,ErrorTimes = @ErrorTimes,LastLoginTime = @LastLoginTime,Version = @Version,CityId = @CityId where XX_UserName = @XX_UserName";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@XX_UserName",hw_manger_admin_user.XX_UserName),
				new SqlParameter("@DistrictCode",hw_manger_admin_user.DistrictCode),
				new SqlParameter("@RoleId",hw_manger_admin_user.RoleId),
				new SqlParameter("@XX_Pass",hw_manger_admin_user.XX_Pass),
				new SqlParameter("@CNName",hw_manger_admin_user.CNName),
				new SqlParameter("@ErrorTimes",hw_manger_admin_user.ErrorTimes),
				new SqlParameter("@LastLoginTime",hw_manger_admin_user.LastLoginTime),
				new SqlParameter("@Version",hw_manger_admin_user.Version),
				new SqlParameter("@CityId",hw_manger_admin_user.CityId)
            };
            result = SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
            return result;
        }
        #endregion
		
		#region##统计HW_Manger_Admin_User
		/// <summary>
        /// 修改HW_Manger_Admin_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
		public int SelectCount()
		{
			int i = 0;
            string sql = "select count(*) from HW_Manger_Admin_User";
            i = (int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql);
            return i;
		}
		/// <summary>
        /// 修改HW_Manger_Admin_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int SelectCountById()
        {
            int i=0;
            string sql="select count(XX_UserName) from HW_Manger_Admin_User";
            i = (int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql);
            return i;
        }
		#endregion
        #region##自定义HW_Manger_Admin_User
        public HW_Manger_Admin_User GetByUserName(string userName)
        {
            string sql = @"select * from HW_Manger_Admin_User a inner join HW_District b on a.DistrictCode=b.DistrictCode
                         left join HW_Admin_Detail c on a.XX_UserName=c.XX_UserName
                        where a.XX_UserName=@userName";
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@userName",userName),
            };
            
            //DataSet ds= SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp);
            //return ds.Tables[0];
            return Mapper(SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql, sp));
        }
        public IList<HW_Manger_Admin_User> GetAdminByDisLevel(int level)
        {
            string sql = @"select * from HW_Manger_Admin_User a inner join HW_District b 
                        on a.DistrictCode=b.DistrictCode inner join HW_Admin_Detail c on 
                         a.XX_UserName=c.XX_UserName where b.Level=@level";
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@level",level),
            };
           
            //string sql = "select * from HW_Manger_Admin_User";			
            SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql, sp);
            return MapperList(reader) ;
        }
        public DataTable GetDistrictAdmin(string districtCode)
        {
            string sql = @"select * from HW_Manger_Admin_User a inner join HW_District b 
                        on a.DistrictCode=b.DistrictCode left join HW_District d on a.cityid=d.districtcode left join HW_Admin_Detail c on 
                         a.XX_UserName=c.XX_UserName where b.ParentDistrictCode=@pdistrictCode and a.roleid='1003'";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pdistrictCode",districtCode),
            };
            //string sql = "select * from HW_Manger_Admin_User";			
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp);

            return ds.Tables[0];
        }
        public DataTable GetAdmin(string districtCode,string roleid)
        {
            string sql = @"select * from HW_Manger_Admin_User a inner join HW_District b 
                        on a.DistrictCode=b.DistrictCode left join HW_District d on a.cityid=d.districtcode left join HW_Admin_Detail c on 
                         a.XX_UserName=c.XX_UserName where b.ParentDistrictCode=@pdistrictCode and a.roleid=@roleid";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pdistrictCode",districtCode),
                new SqlParameter("@roleid",roleid),
            };
            //string sql = "select * from HW_Manger_Admin_User";			
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp);

            return ds.Tables[0];
        }
        public DataTable GetDistrictAdmin(int pageIndex,int pageSize,out int count)
        {
            string sql = @"select top(@pageSize) * from HW_Manger_Admin_User a inner join HW_District b 
                        on a.DistrictCode=b.DistrictCode left join HW_District d on a.cityid=d.districtcode left join HW_Admin_Detail c on 
                         a.XX_UserName=c.XX_UserName where a.roleid='1003' and a.XX_UserName not in(select TOP (@pageSize*(@pageIndex)) XX_UserName from HW_Manger_Admin_User where roleid='1003'
                          order by XX_UserName) order by a.XX_UserName";
            //string sql = "select * from HW_Manger_Admin_User";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
            };
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp);

            string sql2 = @"select count(*) from HW_Manger_Admin_User where roleid='1003'";
            count =Convert.ToInt32(SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2));
            return ds.Tables[0];
        }
        public DataTable GetAdminByDisLevel(string pdistrictCode, int level)
        {
            string sql = @"select * from HW_Manger_Admin_User a inner join (select * from HW_District where Level=@Level)  b 
                        on a.DistrictCode=b.DistrictCode left join HW_District d on a.cityid=d.districtcode left join HW_Admin_Detail c on 
                         a.XX_UserName=c.XX_UserName where b.ParentDistrictCode=@pdistrictCode or b.DistrictCode=b.DistrictCode";
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@level",level),
                new SqlParameter("@pdistrictCode",pdistrictCode),
            };
            //string sql = "select * from HW_Manger_Admin_User";			
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp);

            return ds.Tables[0];
        }
        public IList<HW_Manger_Admin_User> GetAdminByLevelBottom(int level)
        {
            string sql = @"select * from HW_Manger_Admin_User a inner join HW_District b 
                        on a.DistrictCode=b.DistrictCode left join HW_Admin_Detail c on 
                         a.XX_UserName=c.XX_UserName where b.Level<@level";
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@level",level),
            };
            //string sql = "select * from HW_Manger_Admin_User";			
            SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql, sp);

            return MapperList(reader);
        }
        public IList<HW_Manger_Admin_User> GetAdminByPid(string pdistrictCode)
        {
            string sql = @"select * from HW_Manger_Admin_User a inner join HW_District b 
                        on a.DistrictCode=b.DistrictCode left join HW_Admin_Detail c on 
                         a.XX_UserName=c.XX_UserName where b.ParentDistrictCode=@pdistrictCode";
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@pdistrictCode",pdistrictCode),
            };
            //string sql = "select * from HW_Manger_Admin_User";			
            SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql, sp);
            return MapperList(reader);
        }
        public DataTable GetAdminByRole(int roleId)
        {
            string sql = @"select * from HW_Manger_Admin_User a inner join HW_District b 
                        on a.DistrictCode=b.DistrictCode left join HW_Admin_Detail c on 
                         a.XX_UserName=c.XX_UserName where a.RoleId=@roleId";
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@roleId",roleId),
            };
            //string sql = "select * from HW_Manger_Admin_User";			
            //SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql, sp);
            DataTable list = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            return list;
        }
        public DataTable GetMyAdmin(int pageIndex,int pageSize,out int count, string districtcode)
        {
            string sql = @"WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@distictcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
SELECT TOP(@pageSize) * FROM dbo.HW_Manger_Admin_User a INNER JOIN m ON a.DistrictCode=m.DistrictCode 
LEFT JOIN dbo.HW_Admin_Detail c ON a.XX_UserName=c.XX_UserName WHERE a.XX_UserName NOT IN(SELECT TOP (@pageSize*(@pageIndex)) XX_UserName FROM dbo.HW_Manger_Admin_User ORDER BY XX_UserName) ORDER BY a.XX_UserName asc";
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@distictcode",districtcode),
            };
            string sql2 = @"WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@distictcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
SELECT Count(a.XX_UserName) FROM dbo.HW_Manger_Admin_User a INNER JOIN m ON a.DistrictCode=m.DistrictCode";
            count=Convert.ToInt32(SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp));
            DataTable list = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            return list;
        }
        public DataTable Search(int pageIndex, int pageSize, out int count, string districtcode, string searchString,string roleid)
        {
            string sql= @";WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@distictcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
SELECT TOP(@pageSize) * FROM dbo.HW_Manger_Admin_User a INNER JOIN m ON a.DistrictCode=m.DistrictCode 
LEFT JOIN dbo.HW_Admin_Detail c ON a.XX_UserName=c.XX_UserName 
WHERE (a.XX_UserName LIKE @searchString OR m.name LIKE @searchString) and a.roleid=@roleid AND a.XX_UserName NOT IN(SELECT TOP (@pageSize*(@pageIndex)) XX_UserName FROM 
dbo.HW_Manger_Admin_User y inner join m on y.districtcode=m.districtcode  WHERE (XX_UserName LIKE @searchString OR m.name LIKE @searchString) and roleid=@roleid ORDER BY XX_UserName) 
ORDER BY a.XX_UserName asc";
            string sql2 = @";WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@distictcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
SELECT COUNT(a.XX_UserName) FROM dbo.HW_Manger_Admin_User a INNER JOIN m ON a.DistrictCode=m.DistrictCode 
LEFT JOIN dbo.HW_Admin_Detail c ON a.XX_UserName=c.XX_UserName 
WHERE (a.XX_UserName LIKE @searchString OR m.NAME LIKE @searchString) and a.roleid=@roleid";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@pageSize",pageSize),
				new SqlParameter("@distictcode",districtcode),
                new SqlParameter("@searchString","%"+searchString+"%"),
                 new SqlParameter("@roleid",roleid),
                
            };
            SqlParameter[] sp2 = new SqlParameter[]
            {
				new SqlParameter("@distictcode",districtcode),
                 new SqlParameter("@searchString","%"+searchString+"%"),
                 new SqlParameter("@roleid",roleid),
            };
            count = Convert.ToInt32(SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp2));
            DataTable list = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            return list;
        }
        /// <summary>
        /// 得到下级自身与下级管理员
        /// </summary>
        /// <param name="districtcode"></param>
        /// <returns></returns>
        public DataTable GetSubAdmin(int pageIndex, int pageSize, out int count, string districtcode)
        {
            string sql = @"select TOP(@pageSize) * from HW_Manger_Admin_User a inner join HW_District b 
on a.DistrictCode=b.DistrictCode left join HW_Admin_Detail c on 
a.XX_UserName=c.XX_UserName where b.ParentDistrictCode=@distictcode or b.DistrictCode=@distictcode 
and a.XX_UserName not in (SELECT TOP (@pageSize*(@pageIndex)) XX_UserName FROM 
dbo.HW_Manger_Admin_User m inner join HW_District n 
on m.DistrictCode=n.DistrictCode where n.ParentDistrictCode=@distictcode or n.DistrictCode=@distictcode  ORDER BY XX_UserName)
ORDER BY a.XX_UserName";
            string sql2 = @"select count(a.XX_UserName) from HW_Manger_Admin_User a inner join HW_District b 
on a.DistrictCode=b.DistrictCode left join HW_Admin_Detail c on 
a.XX_UserName=c.XX_UserName where b.ParentDistrictCode=@distictcode or b.DistrictCode=@distictcode";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@pageSize",pageSize),
				new SqlParameter("@distictcode",districtcode),
            };
            SqlParameter[] sp2 = new SqlParameter[]
            {
				new SqlParameter("@distictcode",districtcode)
            };
            count = Convert.ToInt32(SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp2));
            DataTable list = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
           
            return list;
            //SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql, sp);
            //return MapperList(reader);
        }
        public DataTable GetDistrictAdmin(int pageIndex, int pageSize, out int count, string districtcode)
        {
            string sql = @";WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@distictcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
SELECT TOP(@pageSize) * FROM dbo.HW_Manger_Admin_User a INNER JOIN m ON a.DistrictCode=m.DistrictCode 
LEFT JOIN dbo.HW_Admin_Detail c ON a.XX_UserName=c.XX_UserName 
WHERE a.roleid='1003' AND a.XX_UserName NOT IN(SELECT TOP (@pageSize*(@pageIndex)) XX_UserName FROM 
dbo.HW_Manger_Admin_User y inner join m on y.districtcode=m.districtcode  WHERE roleid='1003' ORDER BY XX_UserName) 
ORDER BY a.XX_UserName asc";
            string sql2 = @";WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@distictcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
SELECT COUNT(a.XX_UserName) FROM dbo.HW_Manger_Admin_User a INNER JOIN m ON a.DistrictCode=m.DistrictCode 
LEFT JOIN dbo.HW_Admin_Detail c ON a.XX_UserName=c.XX_UserName 
WHERE a.roleid='1003'";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@pageSize",pageSize),
				new SqlParameter("@distictcode",districtcode),
            };
            SqlParameter[] sp2 = new SqlParameter[]
            {
				new SqlParameter("@distictcode",districtcode),
            };
            count = Convert.ToInt32(SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp2));
            DataTable list = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            return list;
        }
        /// <summary>
        /// 得到下级中固定的角色
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="districtcode"></param>
        /// <param name="roleid"></param>
        /// <returns></returns>
        public DataTable GetAdmin(int pageIndex, int pageSize, out int count, string districtcode,string roleid)
        {
            string sql = @";WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@distictcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
SELECT TOP(@pageSize) * FROM dbo.HW_Manger_Admin_User a INNER JOIN m ON a.DistrictCode=m.DistrictCode 
LEFT JOIN dbo.HW_Admin_Detail c ON a.XX_UserName=c.XX_UserName 
WHERE a.roleid=@roleid AND a.XX_UserName NOT IN(SELECT TOP (@pageSize*(@pageIndex)) XX_UserName FROM 
dbo.HW_Manger_Admin_User y inner join m on y.districtcode=m.districtcode  WHERE roleid=@roleid ORDER BY XX_UserName) 
ORDER BY a.XX_UserName asc";
            string sql2 = @";WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@distictcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
SELECT COUNT(a.XX_UserName) FROM dbo.HW_Manger_Admin_User a INNER JOIN m ON a.DistrictCode=m.DistrictCode 
LEFT JOIN dbo.HW_Admin_Detail c ON a.XX_UserName=c.XX_UserName 
WHERE a.roleid=@roleid";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@pageSize",pageSize),
				new SqlParameter("@distictcode",districtcode),
                new SqlParameter("@roleid",roleid),
            };
            SqlParameter[] sp2 = new SqlParameter[]
            {
				new SqlParameter("@distictcode",districtcode),
                new SqlParameter("@roleid",roleid),
            };
            count = Convert.ToInt32(SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp2));
            DataTable list = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            return list;
        }
        
        #endregion
        #region 明文密码显示
        public DataTable GetByRole(int pageIndex, int pageSize, out int count, int roleid)
        {
            string sql = @"SELECT TOP(@pageSize) * FROM dbo.HW_Manger_Admin_User a INNER JOIN HW_District m ON a.DistrictCode=m.DistrictCode 
LEFT JOIN dbo.HW_Admin_Detail c ON a.XX_UserName=c.XX_UserName left join TShowPassWord d on a.XX_UserName=d.xx_UserName
WHERE a.roleid=@roleid AND a.XX_UserName NOT IN(SELECT TOP (@pageSize*(@pageIndex)) XX_UserName FROM 
dbo.HW_Manger_Admin_User WHERE roleid=@roleid ORDER BY XX_UserName) 
ORDER BY a.XX_UserName asc";
            string sql2 = @"SELECT count(*) FROM dbo.HW_Manger_Admin_User WHERE roleid=@roleid";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@roleid",roleid),
            };
            SqlParameter[] sp2 = new SqlParameter[]
            {
                new SqlParameter("@roleid",roleid),
            };
            count = Convert.ToInt32(SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp2));
            DataTable list = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            return list;
        }
        public DataTable SearchRole(int pageIndex, int pageSize, out int count, int roleid, string searchString)
        {
            string sql = @"SELECT TOP(@pageSize) * FROM dbo.HW_Manger_Admin_User a INNER JOIN HW_District m ON a.DistrictCode=m.DistrictCode 
LEFT JOIN dbo.HW_Admin_Detail c ON a.XX_UserName=c.XX_UserName left join TShowPassWord d on a.XX_UserName=d.xx_UserName
WHERE a.roleid=@roleid and (m.name like @searchString or a.XX_UserName like @searchString) AND a.XX_UserName NOT IN(SELECT TOP (@pageSize*(@pageIndex)) XX_UserName FROM 
dbo.HW_Manger_Admin_User WHERE roleid=@roleid and (m.name like @searchString or a.XX_UserName like @searchString) ORDER BY XX_UserName) 
ORDER BY a.XX_UserName asc";
            string sql2 = @"SELECT count(*) FROM dbo.HW_Manger_Admin_User a inner join HW_District m on a.DistrictCode=m.DistrictCode WHERE roleid=@roleid and (m.name like @searchString or a.XX_UserName like @searchString)";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@roleid",roleid),
                new SqlParameter("@searchString","%"+searchString+"%"),
            };
            SqlParameter[] sp2 = new SqlParameter[]
            {
                new SqlParameter("@roleid",roleid),
                new SqlParameter("@searchString","%"+searchString+"%"),
            };
            count = Convert.ToInt32(SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp2));
            DataTable list = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            return list;
        }
        public DataTable SearchRole(int pageIndex, int pageSize, out int count, string searchString)
        {
            string sql = @"SELECT TOP(@pageSize) * FROM dbo.HW_Manger_Admin_User a INNER JOIN HW_District m ON a.DistrictCode=m.DistrictCode 
LEFT JOIN dbo.HW_Admin_Detail c ON a.XX_UserName=c.XX_UserName left join TShowPassWord d on a.XX_UserName=d.xx_UserName
WHERE a.roleid in(1002,1003,1011,1012) and (m.name like @searchString or a.XX_UserName like @searchString) AND a.XX_UserName NOT IN(SELECT TOP (@pageSize*(@pageIndex)) XX_UserName FROM 
dbo.HW_Manger_Admin_User WHERE roleid in(1002,1003,1011,1012) and (m.name like @searchString or a.XX_UserName like @searchString) ORDER BY XX_UserName) 
ORDER BY a.XX_UserName asc";
            string sql2 = @"SELECT count(*) FROM dbo.HW_Manger_Admin_User a inner join HW_District m on a.DistrictCode=m.DistrictCode WHERE roleid in(1002,1003,1011,1012) and (m.name like @searchString or a.XX_UserName like @searchString)";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@searchString","%"+searchString+"%"),
            };
            SqlParameter[] sp2 = new SqlParameter[]
            {
                new SqlParameter("@searchString","%"+searchString+"%"),
            };
            count = Convert.ToInt32(SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp2));
            DataTable list = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            return list;
        }
        public DataTable SearchRole(int pageIndex, int pageSize, out int count)
        {
            string sql = @"SELECT TOP(@pageSize) * FROM dbo.HW_Manger_Admin_User a INNER JOIN HW_District m ON a.DistrictCode=m.DistrictCode 
LEFT JOIN dbo.HW_Admin_Detail c ON a.XX_UserName=c.XX_UserName left join TShowPassWord d on a.XX_UserName=d.xx_UserName
WHERE a.roleid in(1002,1003,1011,1012) AND a.XX_UserName NOT IN(SELECT TOP (@pageSize*(@pageIndex)) XX_UserName FROM 
dbo.HW_Manger_Admin_User WHERE roleid in(1002,1003,1011,1012) ORDER BY XX_UserName) 
ORDER BY a.XX_UserName asc";
            string sql2 = @"SELECT count(*) FROM dbo.HW_Manger_Admin_User a inner join HW_District m on a.DistrictCode=m.DistrictCode WHERE roleid in(1002,1003,1011,1012)";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@pageSize",pageSize),
            };
            SqlParameter[] sp2 = new SqlParameter[]
            {
            };
            count = Convert.ToInt32(SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp2));
            DataTable list = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            return list;
        }
        #endregion
        

    }
}
